//Writen by David Delgado
//doubled@live.com

using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Text;
using System.Text.RegularExpressions;
using Microsoft.ConfigurationManagement.ManagementProvider;
using Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine;
using System.Data;
using System.Data.OleDb;

namespace BDPAdd
{
    class Program
    {
        static Log.Logger log;
      
        static void Main(string[] args)
        {
            StringDictionary arguments = parseArgs(args);
            if (arguments.Count == 0)
            {
                return; //arguments invalid
            }
            try
            {
                WqlConnectionManager connection;
                OleDbDataAdapter da,datemp;
                OleDbConnection con;
                DataTable dt, dttemp;
                string[] sitecodes;
                ArrayList boundaries;
                string strboundarytype;

                log = new Log.Logger(arguments["LogFile"]);

                con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+arguments["ExcelFile"]+";Extended Properties=Excel 8.0");
                connection = Connect(arguments["Server"]);

                log.addLine("Getting site codes.");
                //get the various site codes from the spreadsheet
                da = new OleDbDataAdapter(@"Select distinct SiteCode from [List$] where SiteCode <> ''", con);
                dt = new DataTable();
                da.Fill(dt);

                sitecodes = new string[dt.Rows.Count];

                log.addLine("Number of unique site codes = " + sitecodes.Length);

                //fill sitecodes array
                for (int i = 0; i < sitecodes.Length; i++)
                {
                    sitecodes[i] = dt.Rows[i][0].ToString();
                    //log.AddLine("Site Code: " + sitecodes[i]);
                } //end for

                foreach (string sitecode in sitecodes)
                {
                    //get unique branch servers
                    da = new OleDbDataAdapter(@"Select Distinct [Server] from [List$] where SiteCode='" + sitecode + "'", con);
                    dt = new DataTable();
                    da.Fill(dt);

                    log.addLine("Number of servers to be added as branch dps for " + sitecode  + " = " + dt.Rows.Count);

                    if (dt.Rows.Count < 1)
                    {
                        continue; //if no results return continue with next iteration of loop
                    } //end if

                    foreach (DataRow row in dt.Rows)
                    {
                        log.addLine("Validating server: " + row["Server"].ToString());

                        if (!validateServer(connection, row["Server"].ToString(), sitecode))
                        {
                            continue;
                        }
                        //get unique boundaries for specific branch server
                        datemp = new OleDbDataAdapter(@"Select Distinct [Server], [FQDNSuffix], [Boundary] from [List$] where Server = '" + row["Server"] + "' AND SiteCode='" + sitecode + "'", con);
                        dttemp = new DataTable();
                        datemp.Fill(dttemp);

                        log.addLine("Number of protected boundaries to be added for " + row["Server"].ToString() + " = " + dttemp.Rows.Count);
                        
                        if (dttemp.Rows.Count < 1)
                        {
                            continue; //if no results return continue with next iteration of loop
                        } //end if

                        boundaries = new ArrayList();


                        for (int j = 0; j < dttemp.Rows.Count; j++)
                        {
                            log.addLine("Validating boundary: " + dttemp.Rows[j][2].ToString());
                            strboundarytype = getBoundaryType(dttemp.Rows[j][2].ToString());
                            // strboundarytype = getBoundaryType(row["Boundary"].ToString()); <- My bad, this shouldn't have been there.
                            if (!validateBoundary(connection, dttemp.Rows[j][2].ToString(), sitecode) || strboundarytype=="")
                            {
                                continue;
                            }//end if                            

                            boundaries.Add(strboundarytype); //Add boundary type to array
                            boundaries.Add(dttemp.Rows[j][2].ToString()); //Add boundary to array

                        } //end for

                        boundaries.TrimToSize(); //in case any boundaries were not valid, don't want empty values
                        addBranchDP(connection, row["Server"].ToString(), sitecode, (String[])boundaries.ToArray(typeof(String)), dttemp.Rows[0][1].ToString());

                    } //end foreach

                } //end foreach
            }//end try
            catch (Exception e)
            {
                log.addErrorLine(e.Message);
            }//end catch
        } //end Main

        private static bool validateServer(WqlConnectionManager connection, string strsysname, string strsitecode)
        {
            //check the server to make sure it is a client assigned to the site code specified

            try
            {

                IResultObject results = connection.QueryProcessor.ExecuteQuery("select SMSAssignedSites,ClientVersion from SMS_R_System where Name='" + strsysname + "' AND Client='1'");
                ArrayList temp;

                foreach (IResultObject result in results) 
                {
                    if (result["ClientVersion"].StringValue.StartsWith("4")) //check if ConfigMgr Client first
                    {
                        IResultObject site = connection.GetInstance("SMS_Site.SiteCode='" + strsitecode + "'");
                        if(site["Type"].StringValue.Equals("1")) //check if site code is for a secondary site
                        {
                            log.addLine("Site " + strsitecode + " is a secondary site.");
                            strsitecode = site["ReportingSiteCode"].StringValue; //use parent site code instead
                            log.addLine("Parent primary site " + strsitecode + " will be used to validate site assignment.");
                        }
                        temp = new ArrayList(result["SMSAssignedSites"].StringArrayValue);
                        if (temp.Contains(strsitecode)) //check Site assignment
                        {
                            log.addLine(strsysname + " is a ConfigMgr client assigned to site: " + strsitecode);
                            return true; //method returns here code below is not executed
                        }//end for each
                    }//end if
                    else
                    {
                        log.addWarningLine(strsysname + " is not a ConfigMgr 2007 client. Client Version = " + result["ClientVersion"].StringValue);
                        return false;
                    }//end else
                }//end for each                

                //if this code is reached, then means no results returned from query above. System is either not in the DB or Client = 0
                log.addWarningLine(strsysname + " is not a ConfigMgr client assigned to: " + strsitecode);
                return false;

            } //end try
            catch (SmsException e)
            {
                log.addErrorLine(e.Message);
                log.addErrorLine("Could not determine if " + strsysname + " is a ConfigMgr client assigned to: " + strsitecode);
                return false;
            }
            catch (Exception e)
            {
                log.addErrorLine(e.Message);
                log.addErrorLine("Could not determine if " + strsysname + " is a ConfigMgr client assigned to: " + strsitecode);
                return false;
            }

        } //end validateServer()

        private static bool validateBoundary(WqlConnectionManager connection, string strboundary, string strsitecode) 
        {
            try
            {
                IResultObject results = connection.QueryProcessor.ExecuteQuery("select Value from SMS_Boundary where Value='" + strboundary + "' AND SiteCode='" + strsitecode + "'");

                foreach (IResultObject result in results)
                {
                    log.addLine(strboundary + " is a boundary in Site: " + strsitecode);
                    return true;
                    
                } //end foreach
                //no results if execution reaches here
                log.addWarningLine(strboundary + " is not a boundary in Site: " + strsitecode);
                return false;
            }//end try
            catch (SmsException e)
            {
                log.addErrorLine(e.Message);
                log.addWarningLine("Could not determine if " + strboundary + " is a boundary in Site: " + strsitecode);
                return false;
            }//end catch
            catch (Exception e)
            {
                log.addErrorLine(e.Message);
                log.addWarningLine("Could not determine if " + strboundary + " is a boundary in Site: " + strsitecode);
                return false;
            }//end catch
        } //end validateBoundary()


        private static void addBranchDP(WqlConnectionManager connection,string strsysname, string strsitecode,string[] boundaries, string strfqdn)
        {
            IResultObject sitesystem, branchdp, epl,ep;
            Dictionary<string, IResultObject> EmbeddedPropertyList,EmbeddedProperties;

            try
            {               
                //Refer to ConfigMgr SDK for how to read and write Site Control File
                //Also, I gathered required properties for implementing branch DP by 
                //viewing instances of the SMS_SCISysResUse class for a branch DP that was manually added

                //This can be done by executing the following query select * from sms_sci_sysresuse where sitecode='<sitecode>'

                //2 instances required. 1 for site system role and 1 for branch dp role
                log.addLine("Creating SMS_SCI_SysResUse SiteSystem instance.");
                //BEGIN SiteSystem SysResUse Instance
                sitesystem = connection.CreateInstance("SMS_SCI_SysResUse");

                sitesystem["ItemName"].StringValue = "[\"Display=\\\\" + strsysname + "\\\"]MSWNET:[\"SMS_SITE=" + strsitecode + "\"]\\\\" + strsysname + "\\,SMS Site System";
                sitesystem["ItemType"].StringValue = "System Resource Usage";
                sitesystem["NALPath"].StringValue = "[\"Display=\\\\" + strsysname + "\\\"]MSWNET:[\"SMS_SITE=" + strsitecode + "\"]\\\\" + strsysname + "\\";
                sitesystem["NALType"].StringValue = "Windows NT Server";
                sitesystem["NetworkOSPath"].StringValue = "\\\\" + strsysname;
                sitesystem["RoleName"].StringValue = "SMS Site System";
                sitesystem["SiteCode"].StringValue = strsitecode;


                ///BEGIN Embedded Property List - PropList
                EmbeddedPropertyList = sitesystem.EmbeddedPropertyLists;

                //Protected Boundary
                epl = connection.CreateEmbeddedObjectInstance("SMS_EmbeddedPropertyList");
                EmbeddedPropertyList.Add("Protected Boundary", epl);
                epl["PropertyListName"].StringValue = "Protected Boundary";
                epl["Values"].StringArrayValue = boundaries;
                
                sitesystem.EmbeddedPropertyLists = EmbeddedPropertyList;
                //END Embedded PropertyList

                //BEGIN Embedded Properties
                EmbeddedProperties = sitesystem.EmbeddedProperties;

                //Is Protected
                ep = connection.CreateEmbeddedObjectInstance("SMS_EmbeddedProperty");
                EmbeddedProperties.Add("IsProtected", ep);
                ep["PropertyName"].StringValue = "IsProtected";
                ep["Value"].IntegerValue = 1;
                ep["Value1"].StringValue = "";
                ep["Value2"].StringValue = "";

                //Server Remote name
                ep = connection.CreateEmbeddedObjectInstance("SMS_EmbeddedProperty");
                EmbeddedProperties.Add("Server Remote Name", ep);
                ep["PropertyName"].StringValue = "Server Remote Name";
                ep["Value"].IntegerValue = 0;
                ep["Value1"].StringValue = strsysname + "." + strfqdn;
                ep["Value2"].StringValue = "";

                //Server Remote Public Name
                ep = connection.CreateEmbeddedObjectInstance("SMS_EmbeddedProperty");
                EmbeddedProperties.Add("Server Remote Public Name", ep);
                ep["PropertyName"].StringValue = "Server Remote Public Name";
                ep["Value"].IntegerValue = 0;
                ep["Value1"].StringValue = "";
                ep["Value2"].StringValue = "";

                //FDMOperation
                ep = connection.CreateEmbeddedObjectInstance("SMS_EmbeddedProperty");
                EmbeddedProperties.Add("FDMOperation", ep);
                ep["PropertyName"].StringValue = "FDMOperation";
                ep["Value"].IntegerValue = 0;
                ep["Value1"].StringValue = "";
                ep["Value2"].StringValue = "";

                //UserName
                ep = connection.CreateEmbeddedObjectInstance("SMS_EmbeddedProperty");
                EmbeddedProperties.Add("UserName", ep);
                ep["PropertyName"].StringValue = "UserName";
                ep["Value"].IntegerValue = 0;
                ep["Value1"].StringValue = "";
                ep["Value2"].StringValue = "";

                //UseMachineAccount
                ep = connection.CreateEmbeddedObjectInstance("SMS_EmbeddedProperty");
                EmbeddedProperties.Add("UseMachineAccount", ep);
                ep["PropertyName"].StringValue = "UseMachineAccount";
                ep["Value"].IntegerValue = 1;
                ep["Value1"].StringValue = "";
                ep["Value2"].StringValue = "";

                sitesystem.EmbeddedProperties = EmbeddedProperties;
                //END EmbeddedProperties

                //END SiteServer SysResUse Instance

                //BEGIN Branch DP SysResUse Instance
                log.addLine("Creating SMS_SCI_SysResUse Distribution Point instance.");
                branchdp = connection.CreateInstance("SMS_SCI_SysResUse");

                //Properties
                branchdp["ItemName"].StringValue = "[\"Display=\\\\" + strsysname + "\\\"]MSWNET:[\"SMS_SITE=" + strsitecode + "\"]\\\\" + strsysname + "\\,SMS Site System";
                branchdp["ItemType"].StringValue = "System Resource Usage";
                branchdp["NALPath"].StringValue = "[\"Display=\\\\" + strsysname + "\\\"]MSWNET:[\"SMS_SITE=" + strsitecode + "\"]\\\\" + strsysname + "\\";
                branchdp["NALType"].StringValue = "Windows NT Server";
                branchdp["NetworkOSPath"].StringValue = "\\\\" + strsysname;
                branchdp["RoleName"].StringValue = "SMS Distribution Point";
                branchdp["SiteCode"].StringValue = strsitecode;

                //BEGIN EmbeddedPropertyList
                EmbeddedPropertyList = branchdp.EmbeddedPropertyLists;

                //Protected Boundary
                epl = connection.CreateEmbeddedObjectInstance("SMS_EmbeddedPropertyList");
                EmbeddedPropertyList.Add("Protected Boundary", epl);
                epl["PropertyListName"].StringValue = "Protected Boundary";
                epl["Values"].StringArrayValue = null;

                //Object Polled By Site Status
                epl = connection.CreateEmbeddedObjectInstance("SMS_EmbeddedPropertyList");
                EmbeddedPropertyList.Add("Objects Polled By Site Status", epl);
                epl["PropertyListName"].StringValue = "Objects Polled By Site Status";
                epl["Values"].StringArrayValue = new string[] { "[\"Display=\\\\" + strsysname + "\\\"]MSWNET:[\"SMS_SITE=" + strsitecode + "\"]\\\\" + strsysname + "\\" };

                branchdp.EmbeddedPropertyLists = EmbeddedPropertyList;
                //END EmbeddedPropertyList

                //BEGIN EmbeddedProperties
                EmbeddedProperties = branchdp.EmbeddedProperties;

                //BITS download
                ep = connection.CreateEmbeddedObjectInstance("SMS_EmbeddedProperty");
                EmbeddedProperties.Add("BITS download", ep);
                ep["PropertyName"].StringValue = "BITS download";
                ep["Value"].IntegerValue = 0;
                ep["Value1"].StringValue = "";
                ep["Value2"].StringValue = "";

                //IsPeerDP
                ep = connection.CreateEmbeddedObjectInstance("SMS_EmbeddedProperty");
                EmbeddedProperties.Add("IsPeerDP", ep);
                ep["PropertyName"].StringValue = "IsPeerDP";
                ep["Value"].IntegerValue = 1;
                ep["Value1"].StringValue = "";
                ep["Value2"].StringValue = "";

                //AllowInternetClients
                ep = connection.CreateEmbeddedObjectInstance("SMS_EmbeddedProperty");
                EmbeddedProperties.Add("AllowInternetClients", ep);
                ep["PropertyName"].StringValue = "AllowInternetClients";
                ep["Value"].IntegerValue = 0;
                ep["Value1"].StringValue = "";
                ep["Value2"].StringValue = "";

                //IsAnonymousEnabled
                ep = connection.CreateEmbeddedObjectInstance("SMS_EmbeddedProperty");
                EmbeddedProperties.Add("IsAnonymousEnabled",ep);
                ep["PropertyName"].StringValue = "IsAnonymousEnabled";
                ep["Value"].IntegerValue = 0;
                ep["Value1"].StringValue = "";
                ep["Value2"].StringValue = "";

                //DPDrive
                ep = connection.CreateEmbeddedObjectInstance("SMS_EmbeddedProperty");
                EmbeddedProperties.Add("DPDrive",ep);
                ep["PropertyName"].StringValue = "DPDrive";
                ep["Value"].IntegerValue = 0;
                ep["Value1"].StringValue = "";
                ep["Value2"].StringValue = "";

                //MinFreeSpace
                ep = connection.CreateEmbeddedObjectInstance("SMS_EmbeddedProperty");
                EmbeddedProperties.Add("MinFreeSpace", ep);
                ep["PropertyName"].StringValue = "MinFreeSpace";
                ep["Value"].IntegerValue = 500;
                ep["Value1"].StringValue = "";
                ep["Value2"].StringValue = "";

                //Server Remote Name
                ep = connection.CreateEmbeddedObjectInstance("SMS_EmbeddedProperty");
                EmbeddedProperties.Add("Server Remote Name", ep);
                ep["PropertyName"].StringValue = "Server Remote Name";
                ep["Value"].IntegerValue = 0;
                ep["Value1"].StringValue = strsysname + "." + strfqdn;
                ep["Value2"].StringValue = "";

                //Server Remote Public Name
                ep = connection.CreateEmbeddedObjectInstance("SMS_EmbeddedProperty");
                EmbeddedProperties.Add("Server Remote Public Name", ep);
                ep["PropertyName"].StringValue = "Server Remote Public Name";
                ep["Value"].IntegerValue = 0;
                ep["Value1"].StringValue = "";
                ep["Value2"].StringValue = "";

                branchdp.EmbeddedProperties = EmbeddedProperties;
                //END Embedded Properties

                log.addLine("Commiting changes.");
                sitesystem.Put();
                branchdp.Put();
            }//end try
            catch (SmsException e)
            {
                log.addErrorLine(e.Message);
                log.addErrorLine("Unable to add " + strsysname + " as a bdp.");
            }//end catch
            catch (Exception e)
            {
                log.addErrorLine(e.Message);
                log.addErrorLine("Unable to add " + strsysname + " as a bdp.");

            }//end catch

        } //end addBranchDP

        //this is straight out of the SDK
        private static WqlConnectionManager Connect(string serverName)
        {
            try
            {
                SmsNamedValuesDictionary namedValues = new SmsNamedValuesDictionary();
                WqlConnectionManager connection = new WqlConnectionManager(namedValues);

                if (System.Net.Dns.GetHostName().ToUpper() == serverName.ToUpper())
                {
                    // Connect to local computer.
                    connection.Connect(serverName);
                }//end if
                else
                {
                    // Connect to remote computer. //removed user name and password from here. caller's credentials will be used
                    connection.Connect(serverName);
                }//end else

                return connection;
            }//end try
            catch (SmsException e)
            {
                log.addErrorLine("Failed to Connect to " + serverName + ". Error: " + e.Message);
                throw;
            }//end catch
            catch (UnauthorizedAccessException e)
            {
                log.addErrorLine("Failed to authenticate. Error:" + e.Message);
                throw;
            }//end catch
        }//end Connect()

        private static string getBoundaryType(string strboundary)
        {
            string strboundarytype = "";

            //ip regular expressions were obtained from http://blogs.msdn.com/mpoulson/archive/2005/01/10/350037.aspx

            Regex iprangepattern = new Regex(@"\A(25[0-5]|2[0-4]\d|[0-1]?\d?\d)(\.(25[0-5]|2[0-4]\d|[0-1]?\d?\d)){3}(\-)(25[0-5]|2[0-4]\d|[0-1]?\d?\d)(\.(25[0-5]|2[0-4]\d|[0-1]?\d?\d)){3}\z", RegexOptions.IgnoreCase);
            Regex ipsubnetpattern = new Regex(@"\A(25[0-5]|2[0-4]\d|[0-1]?\d?\d)(\.(25[0-5]|2[0-4]\d|[0-1]?\d?\d)){3}\z", RegexOptions.IgnoreCase);
            Regex adsitepattern = new Regex(@"^[a-z0-9]+$", RegexOptions.IgnoreCase);

            //HEX Compressed
            Regex ipv6pattern1 = new Regex(@"\A((?:[0-9A-Fa-f]{1,4}(?::[0-9A-Fa-f]{1,4})*)?)::((?:[0-9A-Fa-f]{1,4}(?::[0-9A-Fa-f]{1,4})*)?)\z", RegexOptions.IgnoreCase);
            //6 HEX 4 Dec
            Regex ipv6pattern2 = new Regex(@"\A((?:[0-9A-Fa-f]{1,4}:){6,6})(25[0-5]|2[0-4]\d|[0-1]?\d?\d)(\.(25[0-5]|2[0-4]\d|[0-1]?\d?\d)){3}\z", RegexOptions.IgnoreCase);
            //HEX 4 Dec Compressed
            Regex ipv6pattern3 = new Regex(@"\A((?:[0-9A-Fa-f]{1,4}(?::[0-9A-Fa-f]{1,4})*)?) ::((?:[0-9A-Fa-f]{1,4}:)*)(25[0-5]|2[0-4]\d|[0-1]?\d?\d)(\.(25[0-5]|2[0-4]\d|[0-1]?\d?\d)){3}\z", RegexOptions.IgnoreCase);

            Regex ipv6pattern4 = new Regex(@"\A(?:[0-9a-fA-F]{1,4}:){7}[0-9a-fA-F]{1,4}\z", RegexOptions.IgnoreCase);

            if (iprangepattern.Match(strboundary).Success)
            {
                strboundarytype = "IP Ranges";
            }
            else if (ipsubnetpattern.Match(strboundary).Success)
            {
                strboundarytype = "IP Subnets";
            }
            else if (adsitepattern.Match(strboundary).Success)
            {
                strboundarytype = "AD Site Name";
            }
            else if (ipv6pattern1.Match(strboundary).Success || ipv6pattern2.Match(strboundary).Success || ipv6pattern3.Match(strboundary).Success || ipv6pattern4.Match(strboundary).Success)
            {
                strboundarytype = "IPv6 Prefixes";
            }
            else
            {
                log.addErrorLine("Could not determine boundary type for " + strboundary);
                return strboundarytype;
            }

            log.addLine("Boundary type for " + strboundary + " is " + strboundarytype);
            return strboundarytype;

        } //end getBoundaryType



        //just messing around here
        private static StringDictionary parseArgs(string[] args)
        {

            Regex parameters = new Regex(@"(?<paramname>(/s|/file|/log))[=:\s](?<value>[:\\\.a-z0-9]*)", RegexOptions.IgnoreCase);
            string all = "";
            StringDictionary arguments = new StringDictionary();
           
            foreach (string str in args)
            {
                all = all + str + " ";
            }//end foreach
          
            MatchCollection matches = parameters.Matches(all.Trim());
            if (matches.Count == 3)
            {

                foreach (Match match in matches)
                {

                    if (match.Success)
                    {
                        switch (match.Groups["paramname"].Value.ToUpper())
                        {
                            case "/S":
                                arguments.Add("Server", match.Groups["value"].Value);
                                break;
                            case "/FILE":
                                arguments.Add("ExcelFile", match.Groups["value"].Value);
                                break;
                            case "/LOG":
                                arguments.Add("LogFile", match.Groups["value"].Value);
                                break;
                        }//end switch

                    } //end if

                }//end for each
            } //end if
            else
            {
                Console.WriteLine("Usage: ");
                Console.WriteLine("");
                Console.WriteLine(System.Reflection.Assembly.GetExecutingAssembly().GetName().Name + ".exe /s [central site server] /file [excel file] /log [path]");
                Console.WriteLine("");
                Console.WriteLine("/s \t Central Site Server name.");
                Console.WriteLine("/file \t Path to Excel files.");
                Console.WriteLine("/log \t Log file path.");
                
            } //end else           

            return arguments;

        }//end args Parse

    } //end class Program
} //end namespace DPAdd
